﻿using xCel = Microsoft.Office.Interop.Excel;
using System;
using System.Collections;
using System.ComponentModel;
using System.Globalization;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using System.IO;
using System.Runtime.InteropServices;
using Microsoft.Office.Core;
using Microsoft.Win32;

namespace MaskData
{
    class Excel
    {
        /************************************************************************************
         * ExcelOpenSpreadsheets() is the main function that spawns a new instance of       *
         * Excel. It is is very important because our scanning functions rely on it         *
         * to start the process of actually reading our rows and columns and then calling   * 
         * the hash functions.                                                              *
         ************************************************************************************/

        // Create bool variable to determine how to cleanup
        bool showSuccess = false;

        public void ExcelOpenSpreadsheets(string workbookPath)
        {
            try
            {
                // Initialize an instance of Excel.
                xCel.Application excelApp = new xCel.ApplicationClass();

                // Setup so user can't see Excel.
                excelApp.Visible = true; // Debugging - Make this a feature in options at some point.

                // Open the workbook from the workbookPath variable.
                xCel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                // Activate excelSheets with ALL sheets in workbook.
                xCel.Sheets excelSheets = excelWorkbook.Worksheets;

                ExcelScan(excelSheets, excelApp, excelWorkbook);
            }
            catch (Exception ee)
            {
                MessageBox.Show("Exception: " + ee);
            }
        }

        public void ExcelScan(xCel.Sheets _excelWorksheet, xCel.Application _excelApp,
            xCel.Workbook _excelWorkbook)
        {            
            // Instantiate a new instance of mdOptions so we
            // can access our objects.
            mdMain _mdMain = new mdMain();

            // Read what the values are for the checkboxes first and assign them to a string.            
            string _excelEnable = Convert.ToString(Registry.GetValue(@"HKEY_CURRENT_USER\Software\Mask Data\", "ExcelEnableHash", "Unchecked"));
            string _dataSSN = Convert.ToString(Registry.GetValue(@"HKEY_CURRENT_USER\Software\Mask Data\", "SSNHash", "Unchecked"));
            string _dataCC = Convert.ToString(Registry.GetValue(@"HKEY_CURRENT_USER\Software\Mask Data\", "CCHash", "Unchecked"));
            string _dataWells = Convert.ToString(Registry.GetValue(@"HKEY_CURRENT_USER\Software\Mask Data\", "WellsHash", "Unchecked"));
            string _dataBA = Convert.ToString(Registry.GetValue(@"HKEY_CURRENT_USER\Software\Mask Data\", "BAHash", "Unchecked"));

            try
            {
                foreach (xCel.Worksheet ws in _excelWorksheet)
                {
                    // Grab the range of numbers that populates the XLS.
                    xCel.Range range = ws.UsedRange;

                    // Our main hashing loop, funess, extravaganza!
                    // In the following cases, Value2 returns different types:
                    //
                    // 1. The range variable points to a single cell:
                    // Value2 returns a object
                    //
                    // 2. The range variable points to many cells:
                    // Value2 returns object[,]

                    object[,] values;

                    if (range.Value2.GetType().IsArray)
                    {
                        values = (object[,])range.Value2;
                    }
                    else
                    {
                        values = new object[2, 2];
                        values[1, 1] = range.Value2;
                    }

                    for (int row = 1; row <= range.Rows.Count; row++)
                        for (int col = 1; col <= range.Columns.Count; col++)
                        {
                            // Convert values to strings.
                            string value = Convert.ToString(values[row, col], CultureInfo.InvariantCulture);

                            string replaceSensitive = SHA2Hash.ComputeHash(value, "SHA256", null);

                            // Match expressions to sensitive data and replace with the hash
                            // value.                 
                            if (_excelEnable == "Checked")
                                if (_dataSSN == "Unchecked")
                                    if (_dataCC == "Unchecked")
                                        if (_dataWells == "Unchecked")
                                            if (_dataBA == "Unchecked")
                                            {
                                                if (Regex.IsMatch(value, _mdMain.ssnExpressions))
                                                {
                                                    showSuccess = true;
                                                    range.Cells.set_Item(row, col, replaceSensitive);
                                                }

                                                if (Regex.IsMatch(value, _mdMain.ssnExpressions2))
                                                {
                                                    showSuccess = true;
                                                    range.Cells.set_Item(row, col, replaceSensitive);
                                                }

                                                if (Regex.IsMatch(value, _mdMain.ccExpressions))
                                                {
                                                    showSuccess = true;
                                                    range.Cells.set_Item(row, col, replaceSensitive);
                                                }

                                                if (Regex.IsMatch(value, _mdMain.wfBankAccountExpressions))
                                                {
                                                    showSuccess = true;
                                                    range.Cells.set_Item(row, col, replaceSensitive);
                                                }

                                                if (Regex.IsMatch(value, _mdMain.baBankAccountExpressions))
                                                {
                                                    showSuccess = true;
                                                    range.Cells.set_Item(row, col, replaceSensitive);
                                                }
                                            }

                            // If Excel is enabled, AND ONLY the Social Security type is checkmarked
                            // then find a match and hash.
                            if (_excelEnable == "Checked")
                                if (_dataSSN == "Checked")
                                {
                                    if (Regex.IsMatch(value, _mdMain.ssnExpressions))
                                    {
                                        showSuccess = true;
                                        range.Cells.set_Item(row, col, replaceSensitive);
                                    }
                                    else if (Regex.IsMatch(value, _mdMain.ssnExpressions2))
                                    {
                                        showSuccess = true;
                                        range.Cells.set_Item(row, col, replaceSensitive);
                                    }
                                }

                            // If Excel is enabled, AND ONLY the credit card type is checkmarked
                            // then find a match and hash.
                            if (_excelEnable == "Checked")
                                if (_dataCC == "Checked")
                                {
                                    if (Regex.IsMatch(value, _mdMain.ccExpressions))
                                    {
                                        showSuccess = true;
                                        range.Cells.set_Item(row, col, replaceSensitive);
                                    }
                                }

                            // If Excel is enabled, AND ONLY the Wells Fargo type is checkmarked
                            // then find a match and hash.
                            if (_excelEnable == "Checked")
                                if (_dataWells == "Checked")
                                {
                                    if (Regex.IsMatch(value, _mdMain.wfBankAccountExpressions))
                                    {
                                        showSuccess = true;
                                        range.Cells.set_Item(row, col, replaceSensitive);
                                    }
                                }

                            // If Excel is enabled, AND ONLY the Bank of America type is checkmarked
                            // then find a match and hash.
                            if (_excelEnable == "Checked")
                                if (_dataBA == "Checked")
                                {
                                    if (Regex.IsMatch(value, _mdMain.baBankAccountExpressions))
                                    {
                                        showSuccess = true;
                                        range.Cells.set_Item(row, col, replaceSensitive);
                                    }
                                }
                        }
                    // Release the objects.
                    Marshal.ReleaseComObject(range);
                    Marshal.ReleaseComObject(ws);
                }
            } // ...the hashing loop basically ends here...
            catch (Exception ex)
            {
                // Write error logs to the application path.
                StreamWriter _writeLogs = new StreamWriter(_mdMain.AppPath);
                _writeLogs.WriteLine(ex);
                _writeLogs.Close();
                _writeLogs.Dispose();

                MessageBox.Show(@"An error with the program has occurred, however the program is still operational. 
                                This error has been logged at C:\Program Files\Mask Data\error.txt. 
                                Please send the full log to whughes05@gmail.com. Thank you.");
            }

            // Report success.
            if (showSuccess == true)
            {
                Form newForm = new Form();
                newForm.TopMost = true;
                MessageBox.Show(newForm, "Fields masked successfully.", "Mask Data", MessageBoxButtons.OK);
                newForm.TopMost = false;
            }
            else
            {
                Form newForm = new Form();
                newForm.TopMost = true;
                MessageBox.Show(newForm, @"Mask Data either didn't find anything to mask or you didn't select an option on the main screen.", "Mask Data", MessageBoxButtons.OK);
                newForm.TopMost = false;
            }

            // Shutdown instance of Excel.
            _excelApp.Quit();

            // Release memory.
            GC.Collect();
            GC.WaitForPendingFinalizers();
            try
            {
                Marshal.ReleaseComObject(_excelWorkbook);
                Marshal.ReleaseComObject(_excelApp);
            }
            catch (ArgumentException ae)
            {

            }
        }
    }
}